Release 10.1A: OpenEdge Data Management:
Database Administration

Rebuilding indexes

Use the IDXBUILD (Index Rebuild) qualifier of the PROUTIL utility to:

Compress index blocks to minimize space usage.
Activate all deactivated indexes in the database.
Repair corrupted indexes in the database. (Index corruption is normally signaled by error messages.)
Notes: When you run the Index Rebuild, the database must not be in use.

You perform a backup of your database immediately prior to running an Index Rebuild. Should the Index Rebuild crash due to data corruption, the only method of recovery is a restore from backup.

To run the IDXBUILD qualifier with PROUTIL, enter the following command:

proutil db-name -C idxbuild  [ all |     table [owner-name.]table-name |     area area-name | schema schema-owner |     activeindexes | inactiveindexes ]   [ -T dir-name ]|[ -SS sort-file-directory-specification ]   [ -TB blocksize ][ -TM n ] [ -B n ] [ -SG n ]   [-threads n ] [-threadnum n ]

For more information on each option, see the "PROUTIL IDXBUILD qualifier" section.

When you enter this command without the all, table, area, or schema qualifiers, the following menu appears:

Index Rebuild Utility ===================== Select one of the following: All (a/A) - Rebuild all the indexes Some (s/S) - Rebuild only some of the indexes By Area (r/R) - Rebuild indexes in selected areas By Schema (c/C) - Rebuild indexes by schema owners By Table (t/T) - Rebuild indexes in selected tables By Activation (v/V) - Rebuild selected active or inactive indexes Quit (q/Q) - Quit, do not rebuild Enter your selection:

Use the All option to rebuild all indexes. Use the Some option to rebuild only specific indexes. Use the By Area option to rebuild indexes specific to one or more areas. Use the By Schema option to rebuild indexes owned by one or more schema owners. Use the By Table option to rebuild indexes specific to one or more tables. Use the By Activation option to select active or inactive indexes. After you enter a selection and you qualify those indexes you want to rebuild, the utility prompts if you have enough disk space for index sorting. If you enter yes, the utility sorts the indexes you are rebuilding, generating the indexes in order by their keys. This sorting results in a faster index rebuild and better space use in the index blocks.

To estimate whether you have enough free space to sort the indexes or not, use the following formulas:

If you rebuild all the indexes in your database, sorting the indexes requires up to 75 percent of the total database size in free space.
If you rebuild an individual index, sorting that index requires as much as the following amount of free space:

(size of one index entry) * (number of records in file) * 3

PROUTIL IDXBUILD rebuilds an index or set of indexes in a series of three phases:

The utility scans the database by area, clearing all index blocks that belong to the indexes you are rebuilding and adding those blocks to the free block list.
The utility scans the database by area and rebuilds all the index entries for every data record. If you chose to sort the index, the utility writes the index entries to the sort file. Otherwise, the utility writes the index entries to the appropriate index at this point.
The utility sorts the index entries in the sort file into groups and enters those entries into their respective entries in order, one index at a time, building a compacted index. This phase only occurs if you chose to sort the indexes.

The Index Rebuild qualifier accomplishes most of its work without displaying messages, unless it encounters an error condition.

For Enterprise database licenses, index rebuild is multi-threaded by default. You can specify the maximum number of threads created using the -threadnum n parameter. If not specified, the maximum number of threads created will equal the system’s number of CPUs. The actual number of threads created will not exceed the number of index groups in an area if this value is smaller than the maximum. During a multi-threaded index rebuild, separate threads are assigned the external merging of each index group during Phase 2. Once the main process has created all the threads for Phase 2, it immediately begins building the index tree for Phase 3, enabling Phase 3 to be executed in parallel with Phase 2. If an area has only one index to rebuild, the work will be executed without the use of threads.

If you do not want your index rebuild to be multi-threaded, specify -threads 0. This directs the index rebuild to execute in an unthreaded mode.

If the index rebuild is interrupted while rebuilding selected indexes, the list of selected indexes is retained in a file named dbname.xb. This .xb file is used when the utility is restarted. You do not have to enter the list of indexes manually if the .xb file exists.

Overcoming SRT size limitations

When you run the Index Rebuild utility and choose the Sort option, you might encounter space limitations that can cause the utility to terminate. To overcome this limitation, simply create a file that contains specifications for the directories and the amount of space per directory that you want the SRT file to have access to during the Index Rebuild. The file that contains the specifications must be a text file, must have the same name as the database with an extension of.srt (dbname.srt), and must reside in the same directory as the .db file. In addition, the contents of the file must follow these conventions:

List the directory and the amount of space that you want to allocate to the index rebuild sort on separate lines.
The size that you specify in the dbname.srt directory specification is the maximum (in 1024 byte units) that the file can grow. Specifying 0 for any directory indicates that you want to allow unlimited growth.
Separate the directories from the size by at least one blank.
Terminate the line with a slash (/) followed by end of line.
For threaded index rebuilds, spread the directories across as many devices as possible. In threaded builds, each thread will use the next directory in the sort file, looping back to the beginning of the list, if necessary. If multiple sort files are open on the same disk, you could create significant I/O contention, reducing the performance gain of the threaded rebuild.

For example, if you want to rebuild the index for the sports database and you want the speed sort to have access to 300K of space available in the /user2/db1/first directory, 400K in the user3/junk directory, and unlimited space in the /user4/last directory, then the sports.srt looks like this on UNIX:

300  /user2/db1/first/ 400  /user3/junk/ 0    /user4/last/

and looks like this for Windows:

300  d:\temp 400  e:\temp 0    f:\temp

The Index Rebuild utility accesses the files in the order in which they are listed in the dbname.srt file. So, if you specify an amount of space that is not available, when the disk is filled, then Index Rebuild terminates and the next directory specification is not used. Thus, if a disk has only 200K of space and the dbname.srt specifies 300K, when the 200K is exhausted the Index Rebuild terminates. For example, if /user2/db1/first above does not get 300K of data, Index Rebuild never processes /user3/junk. In addition, if you specify a directory size of 0, any directories specified after it in the dbname.srt are not processed. For these reasons, you should verify that the space you specify in the dbname.srt file is available before running index rebuild.

The Index Rebuild utility opens the files for each of the directories before it actually starts the sort process. As a result, one of the following messages is displayed for each file:

Temporary sort file at pathname used up to nK of disk space.

or:

Temporary sort file at:pathname will use the available disk space.

The previous message occurs even if the .srt file was not found.

When the sort completes, the following message is displayed for each file:

Temporary sort file at pathname used nK of disk space.

In some cases the message displays OK. This simply means that the sort took place completely in memory.

If Index Rebuild does not find a dbname.srt file, then by default, it uses the directory supplied by either the -T parameter or the current working directory.

Maximizing index rebuild performance

To speed up index rebuild operations, do the following:

Answer yes when prompted whether you have enough disk space for sorting.
Increase the Speed Sort (-TB) startup parameter to 24K. (If you are very short of memory, use 16K or 8K.) This improves sort performance; however, it also uses more memory and disk space.
Increase the Merge Number (-TM) startup parameter to 32 (unless memory is scarce).
Use the Sort Grouping (-SG) parameter. A large -SG value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of -TM) and multiply the sum by the speed sort block size (the value of -TB). Memory consumption for each index group equals (-TM + 1) * -TB.
Change the Temporary Directory (-T) startup parameter to store the temporary files on another disk.

The database engine uses the following algorithm to rebuild indexes. For each record, read the index key fields and store in the first available SRT file block. Allocate additional SRT file blocks of the specified block size as required to hold all index keys. Sort the keys in each block then merge the keys to produce a sorted file. A similar technique is used to sort records when there is no index to satisfy a BY clause.

A larger block size can improve index rebuild performance considerably. A larger block size means less SRT block allocation overhead and fewer quicksort operations on individual blocks.

You might have to run the application several times using different block size values to determine the optimal value. If you experience extreme memory shortages when running an OpenEdge session, try setting the block size to 1 to reduce memory consumption.

During index rebuild, try setting -TB to 31, if memory and disk space are available. If the index rebuild fails, try successively smaller values. Remember, a larger value for -TB improves sort performance but uses more memory. The -TB setting has a significant impact on the size of the SRT temporary file. The SRT file size depends on the number of session compile files, and the number and size of sort operations.

Memory usage depends on the number of sorts simultaneously occurring. The simultaneous sorts are logically equivalent to nested FOR EACHstatements. You can estimate memory usage as follows, where M is the estimated memory usage:

M=(sort-block-size)*(number-of-simultaneous-sorts+Merge-Number(-TM)parameter)

Index rebuild always requires eight simultaneous sorts, so during index rebuild:

M=(sort-block-size)*(8+(-TM) parameter)

Therefore, in the default case:

M=(2*(8+5))=26K

Reactivating unique indexes

When reactivating a unique index, IDXBUILD displays the following error message each time it encounters a duplicate index key:

Fix RECID recid, table-name already exists with field-name value.

You must change the record data to eliminate duplicate keys to access all the data with this index. Use another index on the table (if one exists):

FOR EACH table-name USE-INDEX index-without-duplicate-keys: UPDATE table-name.